﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BI;

namespace yunbo.Repositorys.BIRepositorys
{
	public class WLRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";

		/// <summary>
		/// 获取物料
		/// </summary>
		/// <param name="state"></param>
		/// <param name="str"></param>
		/// <returns></returns>
		public List<WL> GetWL(int state, string? str)
		{
			List<WL> wl = new List<WL>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select wl_id, wl_code, wl_name, wl_fl, 
(select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[wl_fl]) wl_fl_name,
wl_size, wl_dw,
(select [dw_name] from [dbo].[bd_dw] where [dw_id]=[wl_dw]) wl_dw_name,
wl_type, (select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[wl_type]) wl_type_name,
wl_ly,  (select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]=[wl_ly]) wl_ly_name,
wl_dj,wl_state
from [dbo].[bd_wl]
where [wl_state]=@state and [wl_isdelete] =1 ";

				if (str != null)
				{
					sql += @" and ([wl_code] like @str or [wl_name] like @str or [wl_size] like @str) ";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					command.Parameters.AddWithValue("@state", state);
					if (str != null)
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							wl.Add(new WL
							{
								wl_id = Convert.ToInt32(reader["wl_id"]),
								wl_code = reader["wl_code"].ToString(),
								wl_name = reader["wl_name"].ToString(),
								wl_fl = Convert.ToInt32(reader["wl_fl"]),
								wl_fl_name = reader["wl_fl_name"].ToString(),
								wl_size = reader["wl_size"].ToString(),
								wl_dw = Convert.ToInt32(reader["wl_dw"]),
								wl_dw_name = reader["wl_dw_name"].ToString(),
								wl_type = Convert.ToInt32(reader["wl_type"]),
								wl_type_name = reader["wl_type_name"].ToString(),
								wl_ly = Convert.ToInt32(reader["wl_ly"]),
								wl_ly_name = reader["wl_ly_name"].ToString(),
								wl_dj = Convert.ToDecimal(reader["wl_dj"]),
								wl_state = Convert.ToInt32(reader["wl_state"])
							});
						}
					}
				}
			}
			return wl;
		}

		/// <summary>
		/// 改变状态
		/// </summary>
		/// <param name="id"></param>
		/// <param name="state"></param>
		/// <returns></returns>
		public bool StateChange(int id, int state)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"update [dbo].[bd_wl] set [wl_state]=@State where [wl_id]=@Id";
					return connection.Execute(sql, new { Id = id, State = state }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"状态更改失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 新增
		/// </summary>
		/// <param name="wl"></param>
		/// <returns></returns>
		public bool AddWL(WL wl)
		{
			string sql = @"insert into [dbo].[bd_wl]
(wl_code, wl_name, wl_fl, wl_size, wl_dw, wl_type, wl_ly,wl_dj, wl_create_by)
values
(@code,@name,@fl,@size,@dw,@type,@ly,@dj,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", wl.wl_code);
						command.Parameters.AddWithValue("@name", wl.wl_name);
						command.Parameters.AddWithValue("@fl", wl.wl_fl);
						command.Parameters.AddWithValue("@size", wl.wl_size);
						command.Parameters.AddWithValue("@dw", wl.wl_dw);
						command.Parameters.AddWithValue("@type", wl.wl_type);
						command.Parameters.AddWithValue("@ly", wl.wl_ly);
						command.Parameters.AddWithValue("@dj", wl.wl_dj);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 修改
		/// </summary>
		/// <param name="wl"></param>
		/// <returns></returns>
		public bool UpdateWL(WL wl)
		{
			string sql = @"update [dbo].[bd_wl] set 
wl_name=@name, wl_fl=@fl, wl_size=@size, wl_dw=@dw, wl_type=@type, wl_ly=@ly,wl_dj=@dj,
[wl_update_by]=1,[wl_update_time]=getdate()
where [wl_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@name", wl.wl_name);
						command.Parameters.AddWithValue("@fl", wl.wl_fl);
						command.Parameters.AddWithValue("@size", wl.wl_size);
						command.Parameters.AddWithValue("@dw", wl.wl_dw);
						command.Parameters.AddWithValue("@type", wl.wl_type);
						command.Parameters.AddWithValue("@ly", wl.wl_ly);
						command.Parameters.AddWithValue("@dj", wl.wl_dj);
						command.Parameters.AddWithValue("@id", wl.wl_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}

		/// <summary>
		/// 删除
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelWL(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_wl] 
                       SET wl_isdelete = 0 
                       WHERE wl_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}

		/// <summary>
		/// 批量删除
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelWLs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_wl] 
		                                 SET wl_isdelete = 0 
		                                 WHERE wl_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

        public List<WL> CHWL()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = @"SELECT 
                            wl.wl_id,
                            wl.wl_code,
                            wl.wl_name,
                            wl.wl_fl,
                            wl.wl_dj,
                            fl_dict.dictionary_name AS wl_fl_name, -- wl_fl 对应的字典名称
                            wl.wl_size,
                            wl.wl_dw,
                            dw.dw_name AS wl_dw_name, -- wl_dw 对应的单位名称
                            wl.wl_type,
                            type_dict.dictionary_name AS wl_type_name, -- wl_type 对应的字典名称
                            wl.wl_ly,
                            wl.wl_state
                        FROM 
                            [dbo].[bd_wl] AS wl
                        LEFT JOIN 
                            [dbo].[dictionary] AS fl_dict 
                        ON 
                            wl.wl_fl = fl_dict.dictionary_id 
                            AND fl_dict.dictionary_type = 8-- 假设 wl_fl 对应的字典类型为 1
                        LEFT JOIN 
                           [dbo].[bd_dw]  AS dw 
                        ON 
                            wl.wl_dw = dw.dw_id -- wl_dw 对应单位表的 dw_id
                        LEFT JOIN 
                            [dbo].[dictionary] AS type_dict 
                        ON 
                            wl.wl_type = type_dict.dictionary_id 
                            AND type_dict.dictionary_type = 9; -- 假设 wl_type 对应的字典类型为 3";
                List<WL> wl = connection.Query<WL>(sql).ToList();
                return wl;
            }
        }

    }
}
